﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using 三层架构.Model;

namespace 三层架构.DAL
{
    class CustomerDAL
    {
        //根据Id获取GetById,Insert,Update,DeleteById,GetAll,GetPagedData(分页数据)

        /// <summary>
        /// 把公共代码封装到一个方法里，提高复用性
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private Customer ToCustomer(DataRow row)
        {
            Customer cust = new Customer();
            cust.Id = (long)row["Id"];
            cust.Name = (string)row["Name"];
            cust.BirthDay = (DateTime?)SqlHelper.FromDBValue(row["BirthDay"]);
            cust.Address = (string)row["Address"];
            cust.CustLevel = (int)row["CustLevel"];
            cust.TelNum = (string)row["TelNum"];
            return cust;
        }


        public Customer GetById(long id)
        {
            DataTable dt = SqlHelper.ExcuteDataTable("select * from T_Customer where Id=@id",
                new SqlParameter("@Id", id));
            if (dt.Rows.Count<=0)
            {
                return null;
            }
            else if (dt.Rows.Count>1)
            {
                throw new Exception("存在多条数据");
            }
            else
            {
                DataRow row = dt.Rows[0];
                //Customer cust = new Customer();
                //cust.Id = (long)row["Id"];
                //cust.Name = (string)row["Name"];
                //cust.BirthDay = (DateTime?)SqlHelper.FromDBValue(row["BirthDay"]);
                //cust.Address = (string)row["Address"];
                //cust.CustLevel = (int)row["CustLevel"];
                //cust.TelNum=(string)row["TelNum"];
                //return cust;
                return ToCustomer(row);
            }
        }

        public void DeleteById(long id)
        {
            SqlHelper.ExecuteNonQuery("delete from T_Customer where Id=@Id",
                new SqlParameter("@Id", id));
        }
        public void Insert(Customer Customer)
        {
            SqlHelper.ExecuteNonQuery(@"INSERT INTO [mytest].[dbo].[T_Customer]
           ([Name]
           ,[BirthDay]
           ,[Address]
           ,[TelNum]
           ,[CustLevel])
     VALUES
           (@Name,@BirthDay, @Address,@TelNum,@CustLevel)",
               new SqlParameter("@Name",Customer.Name),
            new SqlParameter("@BirthDay",SqlHelper.ToDBValue(Customer.BirthDay)),
            new SqlParameter("@Address",Customer.Address),
            new SqlParameter("@TelNum",Customer.TelNum),
            new SqlParameter("@CustLevel",Customer.CustLevel));
        }
        public void Update(Customer Customer)
        {
            SqlHelper.ExecuteNonQuery(@"UPDATE [mytest].[dbo].[T_Customer]
                SET [Name] = @Name
                ,[BirthDay] = @BirthDay
                ,[Address] = @Address 
                ,[TelNum] = @TelNum
                ,[CustLevel] = @CustLevel
                WHERE Id=@Id",
                new SqlParameter("@Name", Customer.Name),
                new SqlParameter("@BirthDay", SqlHelper.ToDBValue(Customer.BirthDay)),
                new SqlParameter("@Address", Customer.Address),
                new SqlParameter("@TelNum", Customer.TelNum),
                new SqlParameter("@CustLevel", Customer.CustLevel));
        }
        public Customer[] GetAll()
        {
            DataTable dt = SqlHelper.ExcuteDataTable("select * from T_Customer");
            Customer[] customers = new Customer[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count;i++ )
            {
                DataRow row = dt.Rows[i];
                //Customer cust = new Customer();
                //cust.Id = (long)row["Id"];
                //cust.Name = (string)row["Name"];
                //cust.BirthDay = (DateTime?)SqlHelper.FromDBValue(row["BirthDay"]);
                //cust.Address = (string)row["Address"];
                //cust.CustLevel = (int)row["CustLevel"];
                //cust.TelNum = (string)row["TelNum"];
                //customers[i] = cust;
                customers[i]=ToCustomer(row);
             }
            return customers;
        }
    }
}
